using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{
    /// <summary>
    /// Description: Concrete DataProvider for table Client. Provides atomic API functions.
    /// Author:  phong.huynh
    /// Created: 13/08/2010 8:25:40 SA
    /// </summary>
    public class CLIENTSDataAPI
    {
        private SQLHelper _dbTool = new SQLHelper();


        // ************************************
        // Implements function getByColumnName
        // ************************************

        public DataTable getByCLI_ID(System.Int32 CLI_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID, CLI_ID) };
            return _dbTool.executeReader("SP_SEL_CLIENTS_getByCLI_ID", parm);
        }

        public DataTable getByACC_ID(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_CLIENTS_getByACC_ID", parm);
        }

        public DataTable getByCLI_NAME(System.String CLI_NAME)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_NAME, CLI_NAME) };
            return _dbTool.executeReader("SP_SEL_CLIENTS_getByCLI_NAME", parm);
        }

        // *************************************************
        // Implement function getAll/getByDynamicCondition
        // *************************************************

        public DataTable getAll()
        {
            return _dbTool.executeReader("SP_SEL_CLIENTS_getAll");
        }

        public DataTable GetAllClient(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SelectClient", parm);
        }
     
        public DataTable getAll_APP(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID , ACC_ID) };
            return _dbTool.executeReader("SP_SEL_CLIENTS_LOADING", parm);
        }

        public bool status_WAIT_DENY(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID , APP_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_INS_STATUSAPP_WAIT_DENY", parm);

            return affectRows == 1;
        }



        public DataTable getPayType()
        {
            return _dbTool.executeReader("SP_GET_PAYTYPE");
        }
        
        // ********************************************************
        // Implement function getByPrimaryKeys / getByForeignKeys
        // In case table have composite primary/foreign keys
        // ********************************************************

        public DataTable getByPrimaryKeys(System.Int32 CLI_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID, CLI_ID) };
            return _dbTool.executeReader("SP_SEL_CLIENTS_getByPrimaryKeys", parm);
        } 

        public bool insertCLIENTS( System.Int32 ACC_ID, System.String CLI_NAME, System.String CLI_ADDRESS, System.String CLI_EMAIL, System.Int32 CLI_PHONE)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID,1),
                                    new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID),
                                    new SqlParameter(CLIENTSDTO._CLI_NAME,CLI_NAME),
                                    new SqlParameter(CLIENTSDTO._CLI_ADDRESS,CLI_ADDRESS),
                                    new SqlParameter(CLIENTSDTO._CLI_EMAIL,CLI_EMAIL),
                                    new SqlParameter(CLIENTSDTO._CLI_PHONE,CLI_PHONE)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_CLIENTS_values", parm);            
            return (effectedRows == 1);
        }
        public int insertClientOutID(System.Int32 ACC_ID, System.String CLI_NAME, System.String CLI_EMAIL, System.String CLI_PHONE,System.String birthday)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID),
                                    new SqlParameter(CLIENTSDTO._CLI_NAME,CLI_NAME),                                    
                                    new SqlParameter(CLIENTSDTO._CLI_EMAIL,CLI_EMAIL),
                                    new SqlParameter(CLIENTSDTO._CLI_PHONE,CLI_PHONE),
                                    new SqlParameter("@BIRTHDAY",birthday)};
            int effectedRows =Convert.ToInt32(_dbTool.ExecuteScalar("SP_INS_CLIENTS_values", parm));
            return effectedRows;
        }
        

        ///<summary>		
        /// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
        ///</summary>

        public bool updateCLIENTS(System.Int32 CLI_ID, System.Int32 ACC_ID, System.String CLI_NAME, System.String CLI_ADDRESS, System.String CLI_EMAIL, System.Int32 CLI_PHONE)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID,CLI_ID),
                                    new SqlParameter(CLIENTSDTO._CLI_NAME,CLI_NAME),
                                    new SqlParameter(CLIENTSDTO._CLI_ADDRESS,CLI_ADDRESS),
                                    new SqlParameter(CLIENTSDTO._CLI_EMAIL,CLI_EMAIL),
                                    new SqlParameter(CLIENTSDTO._CLI_PHONE,CLI_PHONE)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_CLIENTS_update", parm);

            return affectRows == 1;
        }

        ///<summary>
        /// Kie^?m tra trong Category
        /// Du+. tre^n kho'a chi'nh cu?a ddo^'i tu+o+.ng DTO ddu+o+.c truye^`n va`o:
        ///		+ Ti`m tha^'y ddo^'i tu+o+.ng tru`ng kho'a -> ca^.p nha^.t (update)
        ///		+ Kho^ng ti`m tha^'y -> the^m mo+'i (insert)
        ///</summary>		
        ///<returns>True la` tha`nh co^ng</returns>
        public bool insertOrUpdateCLIENTS(System.Int32 ACC_ID, System.String CLI_NAME, System.String CLI_ADDRESS, System.String CLI_EMAIL, System.Int32 CLI_PHONE)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID,ACC_ID),
                                    new SqlParameter(CLIENTSDTO._CLI_NAME,CLI_NAME),
                                    new SqlParameter(CLIENTSDTO._CLI_ADDRESS,CLI_ADDRESS),
                                    new SqlParameter(CLIENTSDTO._CLI_EMAIL,CLI_EMAIL),
                                    new SqlParameter(CLIENTSDTO._CLI_PHONE,CLI_PHONE)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_IU_CLIENTS_insertOrUpdate", parm);


            return effectedRows == 1;
        }


        // ********************************************************************************
        // Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
        // ********************************************************************************

        // return number of deleted rows		
        public int deleteAll()
        {
            return _dbTool.ExecuteNoneQuery("SP_DEL_CLIENTS_deleteAll");
        }

        // return number of deleted rows
        public int deleteByPrimaryKeys(System.Int32 CLI_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID, CLI_ID) };
            return _dbTool.ExecuteNoneQuery("SP_DEL_CLIENTS_deleteByPrimaryKeys", parm);
        }

        public DataTable getSearchResult(System.Int32 ACC_ID, System.String searchPhrase)
        {
            SqlParameter [] parm = {new SqlParameter(CLIENTSDTO._ACC_ID , ACC_ID),
                                    new SqlParameter("@searchPhrase", searchPhrase)};
            return _dbTool.executeDataTable("SP_SEL_CLIENTS_getsearchResult", parm);
        }        
    }
}